CREATE TABLE [dbo].[tb_LineasDeBYP]
(
[idLineaDeBYP] [int] NOT NULL IDENTITY(1, 1),
[Descripcion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[idPeriodoLiq] [int] NOT NULL,
[idTipoBecaOprestamo] [int] NOT NULL,
[FechaOtorga] [datetime] NOT NULL,
[FechaVencim] [datetime] NULL,
[MontoMensual] [float] NOT NULL CONSTRAINT [DF_tb_TiposDeBYP_MontoMensual] DEFAULT ((0)),
[MontoTotal] [float] NOT NULL CONSTRAINT [DF_tb_TiposDeBYP_MontoTotal] DEFAULT ((0)),
[idNivel] [int] NULL,
[idModalidad] [int] NULL,
[MantieneBenefDe] [int] NULL,
[Norma] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumeroPeriodo] [int] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[ControlarNumeroPeriodo] ON [dbo].[tb_LineasDeBYP] 
FOR INSERT, UPDATE 
AS
SET NOCOUNT ON
DECLARE @numeroPeriodo as int


SELECT  @numeroPeriodo=numeroperiodo  FROM inserted



IF (@numeroperiodo) not in (0,1,2,3,4,5,6)
BEGIN

	ROLLBACK transaction 
	RAISERROR('El Número de período debe estar comprendido entre 0 y 6 !!! (0 = anual).', 16,1)
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[GenerarBeneficiariosLinea] ON [dbo].[tb_LineasDeBYP] 
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @mantieneBenefde as int, @IdLineaDeByP as int,@FechaOtorgamiento as datetime,@FechaVencimiento as datetime,@MontoMensual as float,@MontoTotal as float


SELECT  @mantieneBenefde=mantienebenefde,@IdLineaDeByP=IdLineaDeByP,@FechaOtorgamiento=FechaOtorga,@FechaVencimiento =FechaVencim,@MontoMensual= MontoMensual,@MontoTotal=MontoTotal  FROM inserted

IF @mantieneBenefde is not null
BEGIN
	IF (SELECT count( *) FROM tb_BecasYPrestamos WHERE lineadebyp=@mantieneBenefde) <1
	 BEGIN
		ROLLBACK transaction 
		RAISERROR('No existen Beneficiarios  asociados a esta LINEA.', 16,1)
	 END
	ELSE
		BEGIN
			INSERT INTO tb_BecasYprestamos([Alumno], [LineaDeByP], [FechaOtorgamiento], [FechaVencimiento], [MontoMensual], [MontoTotal], [MontoRendido], [MontoDevuelto], [CuentaBancaria], [MontoLiquidado], [Expediente])
			SELECT [Alumno],@IdLineaDeByP, @FechaOtorgamiento, @FechaVencimiento, @MontoMensual, @MontoTotal, [MontoRendido], [MontoDevuelto], [CuentaBancaria], @MontoMensual, [Expediente]
			FROM tb_becasyprestamos WHERE lineadebyp in (@mantieneBenefde)--@mantieneBenefde+5
		END
END








GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[SoloModificarDescripcion] ON [dbo].[tb_LineasDeBYP]
for  UPDATE 
AS
SET NOCOUNT ON
DECLARE @mantieneBenefde as int, @IdLineaDeByP as int,@FechaOtorgamiento as datetime,@FechaVencimiento as datetime,@MontoMensual as float,@MontoTotal as float,@idperiodoliq as int,@idtipoBecaoPrestamo as int,@idnivel as int,@idmodalidad as int, @Descripcion as varchar(50), @norma as varchar(50)
DECLARE @mantieneBenefde2 as int, @IdLineaDeByP2 as int,@FechaOtorgamiento2 as datetime,@FechaVencimiento2 as datetime,@MontoMensual2 as float,@MontoTotal2 as float,@idperiodoliq2 as int,@idtipoBecaoPrestamo2 as int,@idnivel2 as int,@idmodalidad2 as int, @Descripcion2 as varchar(50), @norma2 as varchar(50)
declare @msg as varchar

SELECT   @IdLineaDeByP=IdLineaDeByP,@FechaOtorgamiento=FechaOtorga,@FechaVencimiento =FechaVencim,@MontoMensual= MontoMensual,@MontoTotal=MontoTotal,@idperiodoliq= idperiodoliq,@idtipoBecaoPrestamo=idtipoBecaoPrestamo,@idnivel =idnivel,@idmodalidad =idmodalidad, @Descripcion =Descripcion,@mantieneBenefde=mantienebenefde,@norma=Norma  FROM inserted

SELECT   @IdLineaDeByP2=IdLineaDeByP,@FechaOtorgamiento2=FechaOtorga,@FechaVencimiento2 =FechaVencim,@MontoMensual2= MontoMensual,@MontoTotal2=MontoTotal,@idperiodoliq2= idperiodoliq,@idtipoBecaoPrestamo2=idtipoBecaoPrestamo,@idnivel2 =idnivel,@idmodalidad2 =idmodalidad, @Descripcion2 =Descripcion,@mantieneBenefde2=mantienebenefde,@norma2=Norma  FROM deleted


if (@mantienebenefde<>@mantienebenefde2) or (@FechaOtorgamiento<>@FechaOtorgamiento2) or  (@FechaVencimiento<>@FechaVencimiento2) or (@MontoMensual<>@MontoMensual2) or  (@MontoTotal<>@MontoTotal2) or ( @idperiodoliq<>@idperiodoliq2) or  (@idtipoBecaoPrestamo<>@idtipoBecaoPrestamo2) or (@idnivel<>@idnivel2) or (@idmodalidad<>@idmodalidad2 ) or (@Norma<>@Norma2 ) 
 BEGIN
		ROLLBACK transaction 
		RAISERROR('Sólo se puede modificar la DESCRIPCIÓN de la  LINEA.', 16,1)
 END























GO
ALTER TABLE [dbo].[tb_LineasDeBYP] ADD CONSTRAINT [PK_tb_TiposDeBYP] PRIMARY KEY CLUSTERED  ([idLineaDeBYP]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_LineasDeBYP] WITH NOCHECK ADD CONSTRAINT [FK_tb_LineasDeBYP_tb_ModalidadesEnsenanza] FOREIGN KEY ([idModalidad]) REFERENCES [dbo].[tb_ModalidadesEnsenanza] ([idModalidad])
GO
ALTER TABLE [dbo].[tb_LineasDeBYP] WITH NOCHECK ADD CONSTRAINT [FK_tb_LineasDeBYP_tb_NivelesEnsenanza] FOREIGN KEY ([idNivel]) REFERENCES [dbo].[tb_NivelesEnsenanza] ([idNivelEnsenanza])
GO
ALTER TABLE [dbo].[tb_LineasDeBYP] WITH NOCHECK ADD CONSTRAINT [FK_tb_LineasDeBYP_tb_PeriodosDeLiquidacion] FOREIGN KEY ([idPeriodoLiq]) REFERENCES [dbo].[tb_PeriodosDeLiquidacion] ([idPeriodoLiquidacion])
GO
ALTER TABLE [dbo].[tb_LineasDeBYP] WITH NOCHECK ADD CONSTRAINT [FK_tb_LineasDeBYP_tb_TipoBecaOPrestamo] FOREIGN KEY ([idTipoBecaOprestamo]) REFERENCES [dbo].[tb_TipoBecaOPrestamo] ([idTipoBecaoPrestamo])
GO
ALTER TABLE [dbo].[tb_LineasDeBYP] ADD CONSTRAINT [FK_tb_LineasDeBYP_tb_LineasDeBYP1] FOREIGN KEY ([MantieneBenefDe]) REFERENCES [dbo].[tb_LineasDeBYP] ([idLineaDeBYP])
GO
